using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Web.Configuration;


public class DBHelper
{
    private SqlConnection sqlConn = null;
    private string connectString = "";
    private string serverName = "";
    private string databaseName = "";

    public DBHelper()
    {
        connectString = @"Data Source=KNQ8OLATIWECQHS\SQLEXPRESS;Initial Catalog=OnlineOrderDigitalPhotoPrintting;Persist Security Info=True;User ID=sa;Password=123456";

        sqlConn = new SqlConnection(connectString);
    }


    public string getConnectionUrl()
    {
        return connectString;
    }

    public SqlConnection getConnection()
    {
        return sqlConn;
    }

    public SqlDataReader executeQuerySql(string sql)
    {
        SqlCommand sqlComd = null;
        SqlDataReader sqlDr = null;
        try
        {
            Open();
            sqlComd = new SqlCommand(sql, sqlConn);
            sqlDr = sqlComd.ExecuteReader();
        }
        catch (Exception ex)
        {
            throw;
        }
        finally
        {
            //Close();
        }
        return sqlDr;
    }


    public SqlDataReader executeReader(string procName, params SqlParameter[] procParams)
    {
        SqlCommand cmd = null;
        SqlDataReader reader = null;
        try
        {
            Open();
            cmd = new SqlCommand();
            cmd.Connection = sqlConn;
            cmd.CommandText = procName;
            cmd.CommandType = CommandType.StoredProcedure;
            if (procParams != null)
            {
                for (int i = 0; i < procParams.Length; i++)
                {
                    cmd.Parameters.Add(procParams[i]);
                }
            }
            reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch
        {
            throw;
        }
        finally
        {
            Close();
        }
        return reader;
    }

    public int executeNonQuery(string sql)
    {
        SqlCommand cmd = null;
        int num = 0;
        try
        {
            Open();
            cmd = new SqlCommand(sql, sqlConn);
            num = cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw;
        }
        finally
        {
            Close();
        }
        return num;
    }

    public int executeNonQuery(string procName, params SqlParameter[] procParams)
    {
        SqlCommand cmd = null;
        SqlConnection conn = null;
        int affectedRows = 0;
        try
        {
            Open();
            cmd = new SqlCommand();
            cmd.Connection = sqlConn;
            cmd.CommandText = procName;
            cmd.CommandType = CommandType.StoredProcedure;
            if (procParams != null)
            {
                for (int i = 0; i < procParams.Length; i++)
                {
                    cmd.Parameters.Add(procParams[i]);
                }
            }
            affectedRows = cmd.ExecuteNonQuery();
        }
        catch
        {
            throw;
        }
        finally
        {
            if (cmd != null)
            {
                cmd.Dispose();
            }
            Close();
        }
        return affectedRows;
    }

    public object executeScalar(string procName, params SqlParameter[] procParams)
    {
        SqlCommand cmd = null;
        object value;
        try
        {
            Open();
            cmd = new SqlCommand();
            cmd.Connection = sqlConn;
            cmd.CommandText = procName;
            cmd.CommandType = CommandType.StoredProcedure;
            if (procParams != null)
            {
                for (int i = 0; i < procParams.Length; i++)
                {
                    cmd.Parameters.Add(procParams[i]);
                }
            }
            value = cmd.ExecuteScalar();
        }
        catch
        {
            throw;
        }
        finally
        {
            Close();
        }
        return value;
    }

    public DataTable executeDataTable(string sql)
    {
        SqlConnection conn = new SqlConnection(getConnectionUrl());
        SqlDataAdapter adapter = null;
        DataSet ds = new DataSet();
        SqlCommand cmd = null;
        try
        {
            conn.Open();
            cmd = new SqlCommand(sql, conn);
            adapter = new SqlDataAdapter(cmd);
            adapter.Fill(ds);
        }
        catch
        {
            //throw;
        }
        finally
        {
            conn.Close();
            if (adapter != null)
            {
                //adapter.Dispose();

            }
        }
        return ds.Tables[0];
    }

    public DataSet executeDataSet(string procName, params SqlParameter[] procParams)
    {
        SqlDataAdapter adapter = null;
        DataSet ds = new DataSet();
        SqlCommand cmd = null;
        try
        {
            Open();
            cmd = new SqlCommand(procName, sqlConn);
            cmd.CommandType = CommandType.StoredProcedure;
            if (procParams != null)
            {
                for (int i = 0; i < procParams.Length; i++)
                {
                    cmd.Parameters.Add(procParams[i]);
                }
            }
            adapter = new SqlDataAdapter(cmd);
            adapter.Fill(ds);
        }
        catch
        {
            throw;
        }
        finally
        {
            Close();
            if (adapter != null)
            {
                adapter.Dispose();


            }
        }
        return ds;
    }

    public DataSet executeDataSet(string sql)
    {
        SqlDataAdapter adapter = null;
        DataSet ds = new DataSet();
        SqlCommand cmd = null;
        try
        {
            Open();
            cmd = new SqlCommand(sql, sqlConn);
            adapter = new SqlDataAdapter(cmd);
            adapter.Fill(ds);
        }
        catch
        {
            throw;
        }
        finally
        {
            Close();
            if (adapter != null)
            {
                adapter.Dispose();


            }
        }
        return ds;
    }
    public SqlDataAdapter executeDataAdapter(string sql)
    {
        SqlDataAdapter adapter = null;
        try
        {
            Open();
            adapter = new SqlDataAdapter(sql, sqlConn);
            SqlCommandBuilder cb = new SqlCommandBuilder(adapter);
        }
        catch
        {
            throw;
        }
        finally
        {
            Close();
            if (adapter != null)
            {
                //adapter.Dispose();
            }
        }
        return adapter;
    }

    public SqlParameter createParameter(string paramName, object value)
    {
        SqlParameter param = new SqlParameter(paramName, value);
        return param;
    }

    public DataSet getDataSet(String command, String Table)
    {
        DataSet ds = new DataSet();
        Open();
        SqlCommand sqlcom = new SqlCommand();
        SqlDataAdapter sqldatadapter = new SqlDataAdapter();
        sqlcom = sqlConn.CreateCommand();
        sqlcom.CommandText = command;
        sqldatadapter.SelectCommand = sqlcom;
        sqldatadapter.Fill(ds, Table);
        Close();
        return ds;
    }

    public SqlDataReader getUserID(String command)
    {
        Open();
        SqlCommand sqlcom = new SqlCommand();
        sqlcom = sqlConn.CreateCommand();
        sqlcom.CommandText = command;
        SqlDataReader sqldatareader = sqlcom.ExecuteReader();
        Close();
        return sqldatareader;
    }

    public void UpdateField(String command)
    {
        Open();
        SqlCommand sqlcom = new SqlCommand();
        sqlcom = sqlConn.CreateCommand();
        sqlcom.CommandText = command;
        sqlcom.ExecuteNonQuery();
        Close();
    }

    public void Open()
    {
        if (sqlConn != null)
        {
            if (sqlConn.State == ConnectionState.Open)
                sqlConn.Close();
            sqlConn.Open();
        }
    }
    public void Close()
    {
        if (sqlConn != null)
        {
            if (sqlConn.State == ConnectionState.Closed)
                sqlConn.Open();
            sqlConn.Close();
        }
    }
}

